''' <summary>
''' Display list of available Working tables
''' </summary>
''' <remarks>Starting 8/16/2010 no longer have complication of displaying by Project; in other words, there will be no provision for storing multiple projects per database</remarks>
Public Class frmOpen
    Inherits System.Windows.Forms.Form
    Private WithEvents bwNumRecords As System.ComponentModel.BackgroundWorker
    Private dtTables As DataTable
    Private TrackingColumns As New Generic.List(Of String)

    Private ThisProject As clsProject
    Private MultiSelect As Boolean
    Private DefaultTable As String
    Friend SelectedTables As New Generic.List(Of String)

    <CLSCompliant(False)> _
    Public Sub New(ByVal _Project As clsProject, ByVal _MultiSelect As Boolean, Optional ByVal _DefaultTable As String = "")
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call
        ThisProject = _Project
        MultiSelect = _MultiSelect
        DefaultTable = _DefaultTable
    End Sub

    Private Sub btnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        ProgressCancel = True
        Close()
    End Sub

    Private Sub btnCreate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        'Close()
        If Not HasTablePermissions(ThisProject) Then Exit Sub
        Dim NewTable As String = MainForm.FileNewWorking(False)
        FillWithTables()
        SelectedTables.Clear()
        SelectedTables.Add(NewTable)
    End Sub

    Private Sub btnSelect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelect.Click
        MainForm.WriteStatus("Waiting for SQL completion...")
        Hide()
        'CancelQuery()
        ProgressCancel = True 'in case is currently looping to get the number of records
        MainForm.ProgressComplete()
        With dgTables
            SelectedTables.Clear()
            For i As Integer = 0 To .RowCount - 1
                If .Rows(i).Selected Then SelectedTables.Add(.Rows(i).Cells("Table Name").Value)
            Next
            'If .CurrentRow Is Nothing Then
            '    SelectedTables = .Rows(0).Cells("Table Name").Value
            'Else
            '    SelectedTables = .CurrentRow.Cells("Table Name").Value
            'End If
        End With
        Close()
    End Sub

    Private Sub bwNumRecords_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bwNumRecords.DoWork
        For Each row As DataRow In dtTables.Rows
            Application.DoEvents()
            Dim TableName As String = row.Item("Table Name").ToString
            Try
                Dim NumRecords As Integer = ThisProject.DB.GetRecordCount(TableName, 1)
                ThisProject.UpdateTrackingRecord(TableName, NumRecords)
                If Not IsDBNull(TableName) Then
                    row.Item("No. Records") = IIf(NumRecords = -1, "Unknown", NumRecords)
                End If
            Catch ex As Exception
            End Try
            If bwNumRecords.CancellationPending Then e.Cancel = True : Exit Sub
        Next
    End Sub

    Private Sub bwNumRecords_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bwNumRecords.RunWorkerCompleted
        dgTables.Refresh()
        bwNumRecords.Dispose()
        bwNumRecords = Nothing
    End Sub

    Private Sub cbo_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboTableGroup.SelectionChangeCommitted
        FilterTables()
    End Sub

    Private Sub dgTables_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgTables.KeyDown
        If e.KeyCode = Keys.Enter Then
            Me.btnSelect.PerformClick()
            e.Handled = True
        End If
    End Sub

    Private Sub dgTables_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgTables.MouseDoubleClick
        Me.btnSelect.PerformClick()
    End Sub

    Friend Sub FillWithTables()
        Try

            If Not MainForm.CheckTableStructure("Tracking") Then Exit Sub

            'look in database for tables and list them; don't include master or support or special system tables

            dgTables.Visible = False

            MainForm.WriteProgress("Getting list of Working tables...", ProgressBarStyle.Marquee)

            'get list of all non-support tables (and also not master for current project)
            Dim colWorkTables As Generic.List(Of String) = ThisProject.GetTableList(clsProject.enumTableTypes.Working)

            'get list of all master tables for all projects from Tracking table (don't want to include these in Working table list)
            Dim colMasterTables As Generic.List(Of String) = ThisProject.DB.GetRecordList("Tracking", "Master_DB_Name")

            'force all to upper case
            For i As Integer = 0 To colMasterTables.Count - 1
                colMasterTables(i) = colMasterTables(i).ToUpper
            Next

            'remove entries
            For i As Integer = colWorkTables.Count - 1 To 0 Step -1
                If colMasterTables.Contains(colWorkTables(i).ToUpper) Then colWorkTables.RemoveAt(i)
            Next

            dtTables = New DataTable

            With dtTables.Columns
                .Clear()
                .Add("Table Name", GetType(String)).MaxLength = 100
                .Add("Description", GetType(String)).MaxLength = 100
                .Add("Source", GetType(String)).MaxLength = 100
                .Add("Table Group", GetType(String)).MaxLength = 100
                .Add("No. Records", GetType(String)).MaxLength = 20
                .Add("Last Updated", GetType(String)).MaxLength = 20
            End With

            dtTables.Rows.Clear()
            Dim dtTracking As DataTable = Nothing
            ThisProject.DB.AddTable(dtTracking, $"SELECT Temp_DB_Name,Description,Source,TableGroup,Record_Count,Create_Update FROM {ThisProject.FullTableName(clsProject.enumTableTypes.Tracking)}")

            For i As Integer = 0 To colWorkTables.Count - 1
                Dim TableName As String = colWorkTables(i)
                If TableName.StartsWith(clsProject.DataPrefix, StringComparison.OrdinalIgnoreCase) Then Continue For
                Dim NewRow As DataRow = dtTables.NewRow
                With NewRow
                    .Item("Table Name") = TableName
                    .Item("Description") = "*Not Found In Tracking Table*"
                    .Item("Table Group") = ""
                    For Each row As DataRow In dtTracking.Rows
                        If String.Equals(row.Item("Temp_DB_Name").ToString, TableName, StringComparison.OrdinalIgnoreCase) Then
                            .Item("Description") = TestNull(row.Item("Description"), "")
                            .Item("Source") = TestNull(row.Item("Source"), "")
                            If TrackingColumns.Contains("TABLEGROUP") Then
                                .Item("Table Group") = TestNull(row.Item("TableGroup"), "")
                            Else
                                .Item("Table Group") = ""
                            End If
                            Exit For
                        End If
                    Next

                    Dim NumRecords As Integer, LastUpdated As Date
                    Project.GetTrackingRecord(TableName, NumRecords, LastUpdated)
                    .Item("No. Records") = IIf(NumRecords = -1, "Unknown", NumRecords.ToString("N0"))
                    .Item("Last Updated") = IIf(LastUpdated = "1/1/1900", "Unknown", LastUpdated.ToString(MyDateTimeFormat))
                    dtTables.Rows.Add(NewRow)
                End With
                If Not MainForm.WriteProgress(i, colWorkTables.Count - 1, "Examining Working tables", ProgressBarStyle.Blocks, True) Then Exit For
            Next

            dtTracking.Dispose()

            MainForm.ProgressComplete()

            With dgTables
                .DataSource = dtTables
                .AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
                .AutoResizeRows(DataGridViewAutoSizeRowsMode.AllCells)
                For Each col As DataGridViewColumn In .Columns
                    col.SortMode = DataGridViewColumnSortMode.NotSortable
                Next
                .Columns("No. Records").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                '.Columns("Project Name").Visible = cboProject.SelectedIndex = 0
                '.Columns("No. Records").Visible = ThisProject.DisplayRecordCount <> clsProject.enumDisplayRecordCount.Hide
                For Each col As DataGridViewColumn In .Columns
                    col.SortMode = DataGridViewColumnSortMode.Automatic
                Next
                .Visible = True
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub FilterTables()
        Try
            With dgTables
                .Visible = True
                Dim bsTracking As New BindingSource(.DataSource, .DataMember)
                .DataSource = bsTracking
                bsTracking.Filter = ""
                '.Columns("Project Name").Visible = (cboProject.SelectedIndex = 0)
                'If Me.cboProject.SelectedIndex > 0 Then
                '    'bsTracking.Filter = String.Format("([Project Name]='{0}' OR [Project Name]='*Not Specified*')", cboProject.Text.Replace("'", ""))
                'End If
                If Not Me.cboTableGroup.Text.StartsWith("(") Then
                    If Me.cboTableGroup.Text.Contains("'") Then
                        WarningMsg("Table groups must not contain the single quote character: '")
                    Else
                        'bsTracking.Filter &= String.Format(" AND [Table Group]='{0}'", Me.cboTableGroup.Text)
                        bsTracking.Filter = String.Format("[Table Group]='{0}'", Me.cboTableGroup.Text)
                    End If
                End If
                'bsTracking.Sort = "[Project Name],[Table Name]"
                bsTracking.Sort = "[Table Name]"
                .Refresh()
                .AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
                .Visible = True
            End With
            If Not String.IsNullOrEmpty(DefaultTable) Then
                For r As Integer = 0 To dgTables.Rows.Count - 1
                    Debug.Print(dgTables.Item("Table Name", r).Value)
                    If dgTables.Item("Table Name", r).Value = DefaultTable Then
                        dgTables.CurrentCell = dgTables.Item("Table Name", r)
                        Exit For
                    End If
                Next
            End If
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    'Private Sub frmOpen_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Activated
    '    Me.dgTables.Focus()
    '    Static FirstTime As Boolean = True
    '    If FirstTime Then
    '        FirstTime = False
    '        GetNumRecords(False)
    '    End If
    'End Sub

    Private Sub frmOpen_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        ProgressCancel = True
        Application.DoEvents()
        SaveSetting(REGAPP, Me.Name, "LastGroup", cboTableGroup.Text)
        SaveWindowPos(REGAPP, Me)
        MainForm.ProgressComplete()
    End Sub

    Private Sub frmOpen_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            MainForm.WriteProgress("Initializing form...", ProgressBarStyle.Marquee)
            GetWindowPos(REGAPP, Me)
            With dgTables
                .RowHeadersVisible = False
                .ReadOnly = True
                .AllowUserToOrderColumns = False
                .AllowUserToResizeColumns = True
                .AllowUserToResizeRows = False
                .RowHeadersVisible = False
                .ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .SelectionMode = DataGridViewSelectionMode.FullRowSelect
                .MultiSelect = True
                .Focus()
            End With

            Dim tf As Boolean = ThisProject.IsLoggedIn
            btnCreate.Enabled = tf And Project.HasTablePermissions
            btnGroups.Enabled = tf
            btnEditTable.Enabled = tf
            btnEditTracking.Enabled = tf

            SetupForm()
            FillWithTables()
            FilterTables()

        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub SetupForm()
        Dim dt As DataTable = Nothing
        ThisProject.DB.AddTable(dt, $"SELECT * FROM {ThisProject.FullTableName(clsProject.enumTableTypes.Tracking)} WHERE 1=2")
        With TrackingColumns
            .Clear()
            For Each col As DataColumn In dt.Columns
                .Add(col.ColumnName.ToUpper)
            Next
        End With
        dt.Dispose()

        With Me.cboTableGroup
            .Sorted = True
            .Items.Clear()
            .Items.Add("(All Working Tables)")
            .SelectedIndex = 0
        End With

        Dim dtTracking As DataTable = Nothing
        If TrackingColumns.Contains("TABLEGROUP") Then
            ThisProject.DB.AddTable(dtTracking, $"SELECT DISTINCT TableGroup FROM {ThisProject.FullTableName(clsProject.enumTableTypes.Tracking)}")
            For Each row As DataRow In dtTracking.Rows
                Dim tg As String = TestNull(row.Item("TableGroup"), "").ToString
                With cboTableGroup
                    If Not .Items.Contains(tg) And tg <> "" Then .Items.Add(tg)
                End With
            Next
            dtTracking.Dispose()
            Dim LastGroup As String = GetSetting(REGAPP, Me.Name, "LastGroup", "")
            If cboTableGroup.Items.Contains(LastGroup) Then cboTableGroup.Text = LastGroup
        End If
    End Sub

    Private Sub GetNumRecords(ByVal RefreshRecordCount As Boolean)
        Try
            'because it can take a long time to determine the number or records, do that in the background
            'bwNumRecords = New System.ComponentModel.BackgroundWorker
            'With bwNumRecords
            '    .WorkerSupportsCancellation = True
            '    .RunWorkerAsync()
            'End With

            'problem with above, is that there is a delay after the background worker is cancelled; this can cause a subsequent
            'request to open a connection to fail

            'instead, set a timeout on the query and don't run as background worker

            Dim sw As New Stopwatch
            sw.Start()

            ProgressCancel = False
            For r As Integer = 0 To dtTables.DefaultView.Count - 1
                Application.DoEvents()
                If ProgressCancel Then Exit Sub
                dtTables.DefaultView.Item(r).Item("No. Records") = "Refreshing..."
                dgTables.Refresh()
                Application.DoEvents()
                Dim TableName As String = dtTables.DefaultView.Item(r).Item("Table Name").ToString
                Dim NumRecords As Integer = 0
                NumRecords = ThisProject.GetTrackingRecordCount(TableName)
                If RefreshRecordCount Then
                    NumRecords = ThisProject.DB.GetRecordCount(TableName, 30)
                    ThisProject.UpdateTrackingRecord(TableName, NumRecords)
                    If Not IsDBNull(TableName) Then dtTables.DefaultView.Item(r).Item("No. Records") = IIf(NumRecords = -1, "Very Many", NumRecords.ToString("N0"))
                Else
                    If Not IsDBNull(TableName) Then dtTables.DefaultView.Item(r).Item("No. Records") = IIf(NumRecords = -1, "Unknown", NumRecords.ToString("N0"))
                End If
                If Not MainForm.WriteProgress(r, dtTables.Rows.Count - 1, "Getting record counts for each table", ProgressBarStyle.Blocks, False) Then Exit For
                Application.UseWaitCursor = False 'so can pick table without hourglass
                dgTables.Refresh()
            Next
            If sw.Elapsed.Seconds >= RecordCountTimeout Then MainForm.CheckRecordCountWarning(ThisProject)
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            MainForm.ProgressComplete()
        End Try
    End Sub

    Private Sub btnHelp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHelp.Click
        ShowHelp(Me, "11")
    End Sub

    Private Sub mnuCopy_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuCopy.Click
        With dgTables
            .ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
            My.Computer.Clipboard.Clear()
            .MultiSelect = True
            .SelectAll()
            My.Computer.Clipboard.SetDataObject(.GetClipboardContent)
            .MultiSelect = False
            .Rows(0).Selected = True
        End With
    End Sub

    Private Sub lnkUpdate_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles lnkUpdate.LinkClicked
        Try

            With Project
                If lnkUpdate.Text.StartsWith("Cancel") Then 'cancel it
                    ProgressCancel = True
                    Cursor = Cursors.WaitCursor
                Else
                    ProgressCancel = False
                    lnkUpdate.Text = "Cancel Record Count Update"
                    Cursor = Cursors.WaitCursor
                    Application.DoEvents()
                    FillWithTables()
                    FilterTables()
                    GetNumRecords(True)
                    lnkUpdate.Text = "Update Record Counts"
                End If
            End With


        Catch ex As Exception

            ErrorMsg(, ex)

        Finally

            Cursor = Cursors.Default

        End Try

    End Sub

    Private Sub btnGroups_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGroups.Click
        Try
            With New frmSupportGroup
                .InitialGroupType = 2 'select table group option
                .cboAvail.Visible = False
                .lblInfo.Text = "This form helps you to interactively create and maintain table groups. First, the Available Items list will be filled with table names. Next enter a new or select an existing group name on the right. Add and remove table names in the group using the buttons or by double-clicking. To delete a group, simply select it and remove all associated tables."
                .ShowDialog(Me)
                SetupForm()
                FillWithTables()
                FilterTables()
                .Dispose()
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub btnEditTracking_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditTracking.Click
        Dim frm As New frmSupport
        With frm
            If WRDBForms.Contains(.Text) Then CType(WRDBForms(.Text), frmSupport).Close()
            .TabControl1.SelectedIndex = .TabControl1.TabCount - 2
            GetWindowPos(REGAPP, frm)
            Me.Enabled = False
            .ShowDialog(Me)
            SetupForm()
            FillWithTables()
            FilterTables()
            Me.Enabled = True
            .Dispose()
        End With
    End Sub

    Private Sub btnEditTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditTable.Click
        MainForm.EditTableInfo(dgTables.CurrentRow.Cells("Table Name").Value)
        SetupForm()
        FillWithTables()
        FilterTables()
    End Sub

    Private Sub btnJournal_Click(sender As System.Object, e As System.EventArgs) Handles btnJournal.Click
        frmJournal.GetJournalDescription()
    End Sub
End Class
